package com.scu.dao;

import com.scu.entity.ykUser;
import com.scu.utils.JDBCUtil;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ykUserDao {

    public int insert(ykUser user)throws SQLException {
        Connection conn = JDBCUtil.getConnection();

        String sql = "INSERT INTO USER1(user_id,password,name,phone,email,register_date,level,is_illegal) VALUES(?,?,?,?,?,?,?,?)";
        PreparedStatement prep = conn.prepareStatement(sql);
        prep.setInt(1, user.getUser_id());
        prep.setString(2, user.getPassword());
        prep.setString(3, user.getName());
        prep.setString(4, user.getPhone());
        prep.setString(5, user.getEmail());
        prep.setString(6, user.getRegister_date());
        prep.setString(7, user.getLevel());
        prep.setInt(8, user.getIs_illegal());

        //3. 结果
        int row = prep.executeUpdate();

        //4. 关闭连接
        conn.close();

        return row;
    }

    public int delete(int user_id) throws SQLException {
        //1. 连接
        Connection conn = JDBCUtil.getConnection();

        //2. sql
        String sql = "delete from user1 where user_id=?";
        PreparedStatement prep = conn.prepareStatement(sql);
        prep.setInt(1,user_id);
        System.out.println("执行到dao");
        //3. 结果
        int row = prep.executeUpdate();

        //4. 关闭连接
        conn.close();

        return row;
    }

    public static List<ykUser> findAll() throws SQLException {
        //1. 连接
        Connection conn = JDBCUtil.getConnection();

        //2. sql
        String sql = "select * from user1 ";
        PreparedStatement prep = conn.prepareStatement(sql);

        //3. 结果
        ResultSet rs = prep.executeQuery();
        //处理结果
        List<ykUser> list = new ArrayList<ykUser>();
        while(rs.next()){
            //rs代表的就是每一行
            ykUser user = new ykUser();

            int id = rs.getInt("user_id");
            String password = rs.getString("password");
            String name = rs.getString("name");
            String phone = rs.getString("phone");
            String email = rs.getString("email");
            String register_date = rs.getString("register_date");
            String level = rs.getString("level");
            int is_illegal = rs.getInt("is_illegal");



            user.setUser_id(id);
            user.setPassword(password);
            user.setName(name);
            user.setPhone(phone);
            user.setEmail(email);
            user.setRegister_date(register_date);
            user.setLevel(level);
            user.setIs_illegal(is_illegal);


            list.add(user);
        }

        //4. 关闭连接
        conn.close();

        return list;
    }

    public static List<ykUser> findillegal() throws SQLException {
        //1. 连接
        Connection conn = JDBCUtil.getConnection();

        //2. sql
        String sql = "select * from user1 where is_illegal = 1;";
        PreparedStatement prep = conn.prepareStatement(sql);

        //3. 结果
        ResultSet rs = prep.executeQuery();
        //处理结果
        List<ykUser> list = new ArrayList<ykUser>();
        while(rs.next()){
            //rs代表的就是每一行
            ykUser user = new ykUser();

            int id = rs.getInt("user_id");
            String password = rs.getString("password");
            String name = rs.getString("name");
            String phone = rs.getString("phone");
            String email = rs.getString("email");
            String register_date = rs.getString("register_date");
            String level = rs.getString("level");
            int is_illegal = rs.getInt("is_illegal");



            user.setUser_id(id);
            user.setPassword(password);
            user.setName(name);
            user.setPhone(phone);
            user.setEmail(email);
            user.setRegister_date(register_date);
            user.setLevel(level);
            user.setIs_illegal(is_illegal);


            list.add(user);
        }

        //4. 关闭连接
        conn.close();
        System.out.println(list);
        return list;
    }



    public static void main(String[] args) throws SQLException {
        List<ykUser> list = new ykUserDao().findAll();
        System.out.println(list);
    }
}
